import pandas as pd
import pymysql
import uuid
import datetime
import random
import pinyin.cedict
import json
from urllib import request
import time

db = pymysql.connect( "10.2.5.100", "root", "Kc@123456", "helmet_operate_sit" )
cursor = db.cursor()


def device_data():
    getDeviceSql = 'SELECT device_id,fault_id FROM kc_device_quality_fault_t WHERE device_id != \'\''
    cursor.execute( getDeviceSql )
    fetchall = cursor.fetchall()
    date_device = {}
    for chall in fetchall:
        date_device[chall[0]] = chall[1]
    return date_device


def get_user_id(userName):
    getUserId = 'SELECT user_id FROM sys_user WHERE user_realname like  \'%{0}%\''.format( str( userName ).strip() )
    cursor.execute( getUserId )
    userId = cursor.fetchone()

    return userId[0]


def get_execl_data(excelName):
    excel = pd.read_excel( excelName, usecols=[0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13], names=None )
    excel_data_file = excel.values.tolist()
    print( excel_data_file )
    excel_data = []
    for data in excel_data_file:
        if pd.isnull( data[4] ) or data[4] == str( "\\" ):
            data[4] = ' '
        if pd.isnull( data[3] ) or data[3] == str( "\\" ):
            data[3] = ' '
        if pd.isnull( data[1] ) or data[1] == str( "\\" ):
            data[1] = time.strftime( "%Y-%m-%d %H:%M:%S", time.localtime() )
        if pd.isnull( data[6] ) or data[6] == str( "\\" ):
            data[6] = ' '
        if pd.isnull( data[5] ) or data[5] == str( "\\" ):
            data[5] = ' '
        if pd.isnull( data[7] ) or data[7] == str( "\\" ):
            data[7] = ' '

        if pd.isnull( data[8] ) or data[8] == str( "\\" ):
            data[8] = ' '

        if pd.isnull( data[9] ) or data[9] == str( "\\" ):
            data[9] = ' '

        if data[10] == 'open':
            data[10] = '1'
        else:
            data[10] = '0'

        if pd.isnull( data[12] ) or data[12] == str( "\\" ):
            data[12] = ''
        else:
            splce = str( data[12] ).split( "、" )
            opsUserName = splce[0]
            user_id = get_user_id( opsUserName )
            data[12] = user_id

        if data[11] == '是':
            excel_data.append( data )

    return excel_data


def savedata(saveSql):
    for sql in saveSql:
        cursor.execute( sql )
        db.commit()


if __name__ == '__main__':
    mysql_data = device_data()

excel_data = get_execl_data( "/Users/xuyihang/Downloads/副本质量表.xlsx" )

save_sql = []
for excel in excel_data:
    if pd.notnull( excel[0] ):
        excel_ = mysql_data.get( excel[0] )
        if excel_ is not None:
            sql = 'INSERT INTO kc_verify_dev_info_t (id,fault_id,verify_date,verify_user,verify_pheno,remark,bad_disposal,reason,measure,measure_follower,measure_effect,close_loop_state,dock_person) VALUES (UUID(),\'{0}\',\'{1}\',\'{2}\',\'{3}\',\'{4}\',\'{5}\',\'{6}\',\'{7}\',\'{8}\',\'{9}\',\'{10}\',\'{11}\')' \
                .format( excel_, str( excel[1] ).strip(), str( excel[2] ).strip(), str( excel[3] ).strip(),
                         str( excel[4] ).strip(), str( excel[5] ).strip(), str( excel[6] ).strip(),
                         str( excel[7] ).strip(), str( excel[8] ).strip(),
                         str( excel[9] ).strip(), str( excel[10] ).strip(), str( excel[12] ).strip() )
            print( excel[0], excel_, "1" )
            save_sql.append( sql )
        else:
            sql_add_sql = 'SELECT device_id, fault_id FROM kc_device_quality_fault_t WHERE problem_description =\'{0}\''.format(
                str( excel[13] ).strip() )
            # print( sql_add_sql )
            cursor.execute( sql_add_sql )
            fetchone = cursor.fetchone()
            if fetchone is not None:
                sql = 'INSERT INTO kc_verify_dev_info_t (id,fault_id,verify_date,verify_user,verify_pheno,remark,bad_disposal,reason,measure,measure_follower,measure_effect,close_loop_state,dock_person) VALUES (UUID(),\'{0}\',\'{1}\',\'{2}\',\'{3}\',\'{4}\',\'{5}\',\'{6}\',\'{7}\',\'{8}\',\'{9}\',\'{10}\',\'{11}\')' \
                    .format( fetchone[1], str( excel[1] ).strip(), str( excel[2] ).strip(), str( excel[3] ).strip(),
                             str( excel[4] ).strip(), str( excel[5] ).strip(), str( excel[6] ).strip(),
                             str( excel[7] ).strip(), str( excel[8] ).strip(),
                             str( excel[9] ).strip(), str( excel[10] ).strip(), str( excel[12] ).strip() )
                print( excel[0], fetchone[1], "2" )
                save_sql.append( sql )
    else:
        sql_add_sql = 'SELECT device_id, fault_id FROM kc_device_quality_fault_t WHERE problem_description =\'{0}\''.format(
            str( excel[13] ).strip() )

        cursor.execute( sql_add_sql )
        one = cursor.fetchone()
        if one != None:
            sql = 'INSERT INTO kc_verify_dev_info_t (id,fault_id,verify_date,verify_user,verify_pheno,remark,bad_disposal,reason,measure,measure_follower,measure_effect,close_loop_state,dock_person) VALUES (UUID(),\'{0}\',\'{1}\',\'{2}\',\'{3}\',\'{4}\',\'{5}\',\'{6}\',\'{7}\',\'{8}\',\'{9}\',\'{10}\',\'{11}\')' \
                .format( one[1], str( excel[1] ).strip(), str( excel[2] ).strip(), str( excel[3] ).strip(),
                         str( excel[4] ).strip(), str( excel[5] ).strip(), str( excel[6] ).strip(),
                         str( excel[7] ).strip(), str( excel[8] ).strip(),
                         str( excel[9] ).strip(), str( excel[10] ).strip(), str( excel[12] ).strip() )
            print( excel[0], one[1], "3" )
            save_sql.append( sql )

savedata( save_sql )
